/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.sun.courier.dal;

import com.sun.courier.entity.Calculation;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author NamBH
 */
public class CalculationDAL {

    private final static String INSERT = "insert into Calculation(Distance, ChargePerGram) values(?, ?)";
    private final static String UPDATE = "update Calculation set Distance=?, ChargePerGram=? where Id=?";
    private final static String DELETE = "delete Calculation where Id=?";
    private final static String GET_ALL = "select * from Calculation order by Id desc";
    private final static String GET_BY_ID = "select * from Calculation where Id=?";
    private final static String GET_AT_ROW = "WITH CalculationTmp AS ( "
            + "SELECT ROW_NUMBER() OVER (ORDER BY Id desc) AS rownum, * FROM Calculation) "
            + "SELECT * FROM CalculationTmp WHERE rownum = ?";
    ;
    
    private DataAccessHelper dah = new DataAccessHelper();
    private Connection con;

    public CalculationDAL() {
    }

    public boolean add(Calculation obj) {
        try {
            con = dah.getConnection();
            if (con != null) {
                try {
                    PreparedStatement pre = con.prepareStatement(INSERT);
                    pre.setString(1, obj.getDistance());
                    pre.setDouble(2, obj.getChargePerGram());
                    int rs = pre.executeUpdate();
                    if (rs > 0) {
                        return true;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return false;
    }

    public boolean edit(Calculation obj) {
        try {
            con = dah.getConnection();
            if (con != null) {
                try {
                    PreparedStatement pre = con.prepareStatement(UPDATE);
                    pre.setString(1, obj.getDistance());
                    pre.setDouble(2, obj.getChargePerGram());
                    pre.setInt(3, obj.getId());
                    int rs = pre.executeUpdate();
                    if (rs > 0) {
                        return true;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return false;
    }

    public boolean delete(int id) {
        try {
            con = dah.getConnection();
            if (con != null) {
                try {
                    PreparedStatement pre = con.prepareStatement(DELETE);
                    pre.setInt(1, id);
                    int rs = pre.executeUpdate();
                    if (rs > 0) {
                        return true;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return false;
    }

    public ArrayList<Calculation> getAll() {
        ArrayList<Calculation> objs = new ArrayList<>();
        try {
            con = dah.getConnection();
            if (con != null) {
                ResultSet rs = con.prepareStatement(GET_ALL).executeQuery();
                try {
                    while (rs.next()) {
                        Calculation obj = new Calculation();
                        obj.setId(rs.getInt("Id"));
                        obj.setDistance(rs.getString("Distance"));
                        obj.setChargePerGram(rs.getDouble("ChargePerGram"));

                        objs.add(obj);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return objs;
    }

    public Calculation getById(int depId) {
        Calculation obj = null;
        try {
            con = dah.getConnection();
            if (con != null) {
                try {
                    PreparedStatement pre = con.prepareStatement(GET_BY_ID);
                    pre.setInt(1, depId);
                    ResultSet rs = pre.executeQuery();
                    if (rs.next()) {
                        obj = new Calculation();
                        obj.setId(rs.getInt("Id"));
                        obj.setDistance(rs.getString("Distance"));
                        obj.setChargePerGram(rs.getDouble("ChargePerGram"));
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return obj;
    }

    public Calculation getAtRow(int rowNum) {
        Calculation obj = null;
        try {
            con = dah.getConnection();
            if (con != null) {
                try {
                    PreparedStatement pre = con.prepareStatement(GET_AT_ROW);
                    pre.setInt(1, rowNum);
                    ResultSet rs = pre.executeQuery();
                    if (rs.next()) {
                        obj = new Calculation();
                        obj.setId(rs.getInt("Id"));
                        obj.setDistance(rs.getString("Distance"));
                        obj.setChargePerGram(rs.getDouble("ChargePerGram"));
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return obj;
    }
}
